--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

/*DROP TABLE se_pcp_repent;
CREATE TABLE se_pcp_repent AS (SELECT e.* FROM &erp.pcp_repent e WHERE 1=2);
DROP TABLE se_pcp_repdet;
CREATE TABLE se_pcp_repdet AS (SELECT d.* FROM &erp.pcp_repdet d WHERE 1=2);  */

DELETE FROM se_pcp_repent;
DELETE FROM se_pcp_repdet;


--SELECT * FROM &erp.for_identifiant WHERE id2_cod ='53613'



/*SELECT e.rep_cod,e.PERIODE_VAL,e.JOUR_DAT,e.COMBSTO_ID,d.unfab_cod, d.REPC_VAL
FROM   &erp.pcp_repent e, &erp.pcp_repdet d
WHERE e.rep_cod='AN_ART_UNF'
  AND d.parentenreg_id(+)=e.enreg_id */


 INSERT INTO se_pcp_repent
 (SELECT distinct
      'AN_ART_UNF' rep_cod,
      null deb_dat,
      NULL fin_dat,
      To_Char(SYSDATE,'yyyy') periode_val,
      Trunc(SYSDATE,'YEAR') jour_dat,
      Nvl(Decode(p.codgestion,'5',conv.combsto_ref,p.numspecif+0),0) combsto_id,
      NULL unfab_cod,
      NULL plan_cod,
      NULL cpte_cod,
      0 enreg_id,
      NULL libre_val
  FROM uv_pcatalogues c, uv_pproduits p, temp_conv_rgp_combstoref conv
  WHERE c.REPARTPDP IS NOT null AND c.REPARTPDP !=0
    AND c.art=p.id(+)
    AND conv.clergp(+)=p.id ) ;

 DELETE FROM se_pcp_repent WHERE rep_cod='AN_ART_UNF' AND COMBSTO_ID IS NULL ;
 DELETE FROM se_pcp_repent r WHERE NOT EXISTS (SELECT 1 FROM &erp.art_comp c WHERE c.combsto_id= r.combsto_id) ;

 UPDATE se_pcp_repent tf SET enreg_id=Nvl((select Max(enreg_id) FROM &erp.pcp_repent f WHERE f.rep_cod=tf.rep_cod AND f.combsto_id =tf.combsto_id AND f.periode_val=tf.periode_val),0) ;
 UPDATE se_pcp_repent tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0;

 CALL uvse_synchroverserp('rep_cod@deb_dat@periode_val@combsto_id','PCP_REPENT','O') ;

 commit;


 INSERT INTO se_pcp_repdet
 (SELECT distinct
      Nvl((SELECT enreg_id FROM &erp.pcp_repent e
       WHERE e.rep_cod='AN_ART_UNF'
         AND e.combsto_id=Decode(p.codgestion,'5',conv.combsto_ref,p.numspecif)
         AND e.periode_val=To_Char(SYSDATE,'yyyy')
         ),0)   parentenreg_id,
      NULL periode_val,
      NULL jour_dat,
      NULL combsto_id,
      c.four unfab_cod,
      NULL cpte_cod,
      NULL plan_cod,
      0 enreg_id,
      NULL repp_val,
      c.REPARTPDP repc_val,
      NULL libre_val
  FROM uv_pcatalogues c, uv_pproduits p, temp_conv_rgp_combstoref conv
  WHERE c.REPARTPDP IS NOT null
    AND c.art=p.id(+)
    AND conv.clergp(+)=p.id ) ;

 DELETE FROM se_pcp_repdet WHERE parentenreg_id=0 ;

 UPDATE se_pcp_repdet tf SET enreg_id=Nvl((select enreg_id FROM &erp.pcp_repdet f WHERE f.parentenreg_id=tf.parentenreg_id AND f.unfab_cod=tf.unfab_cod),0) ;
 UPDATE se_pcp_repdet tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0;

 CALL uvse_synchroverserp('parentenreg_id@unfab_cod','PCP_REPDET','O') ;


 DELETE FROM &erp.pcp_repdet gg WHERE  NOT EXISTS (SELECT 1 FROM se_pcp_repdet g WHERE gg.enreg_id=g.enreg_id)
          AND  EXISTS (SELECT 1 FROM &erp.pcp_repent g WHERE rep_cod='AN_ART_UNF' AND gg.parentenreg_id=g.enreg_id /*AND (To_Char(SYSDATE,'yyyy')=gg.periode_val)*/);

 DELETE FROM &erp.pcp_repent gg WHERE rep_cod='AN_ART_UNF' /*AND To_Char(SYSDATE,'yyyy')=gg.periode_val*/ AND NOT EXISTS (SELECT 1 FROM se_pcp_repent g WHERE gg.enreg_id=g.enreg_id);



 commit;


select * from (
SELECT &outils.stragg(source) source,rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
FROM (
  SELECT 'uv' source,e.* FROM   se_pcp_repent e
  WHERE e.rep_cod='AN_ART_UNF'

  UNION ALL

  SELECT 'se' source,e.* FROM   &erp.pcp_repent e
  WHERE e.rep_cod='AN_ART_UNF'
  )
GROUP BY rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
HAVING Count(1)!=2) r
ORDER BY combsto_id